const { query } = require('../config/database-sqlite');

// SQLite数据库初始化脚本
const initDatabase = async () => {
  try {
    console.log('🔗 初始化SQLite数据库...');

    // 创建用户表
    await query(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        nickname TEXT,
        avatar TEXT,
        phone TEXT,
        gender INTEGER DEFAULT 0,
        birthday TEXT,
        signature TEXT,
        vip_level INTEGER DEFAULT 0,
        vip_expire_time TEXT,
        status INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✅ 用户表创建成功');

    // 创建歌手表
    await query(`
      CREATE TABLE IF NOT EXISTS artists (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        alias TEXT,
        avatar TEXT,
        description TEXT,
        country TEXT,
        birth_date TEXT,
        gender INTEGER DEFAULT 0,
        status INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
    console.log('✅ 歌手表创建成功');

    // 创建专辑表
    await query(`
      CREATE TABLE IF NOT EXISTS albums (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        artist_id INTEGER,
        cover_url TEXT,
        description TEXT,
        release_date TEXT,
        company TEXT,
        status INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (artist_id) REFERENCES artists(id)
      )
    `);
    console.log('✅ 专辑表创建成功');

    // 创建歌曲表
    await query(`
      CREATE TABLE IF NOT EXISTS songs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        artist_id INTEGER,
        album_id INTEGER,
        duration INTEGER DEFAULT 0,
        file_url TEXT,
        cover_url TEXT,
        lyrics TEXT,
        play_count INTEGER DEFAULT 0,
        like_count INTEGER DEFAULT 0,
        quality INTEGER DEFAULT 1,
        file_size INTEGER DEFAULT 0,
        bitrate INTEGER DEFAULT 0,
        status INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (artist_id) REFERENCES artists(id),
        FOREIGN KEY (album_id) REFERENCES albums(id)
      )
    `);
    console.log('✅ 歌曲表创建成功');

    // 创建歌单表
    await query(`
      CREATE TABLE IF NOT EXISTS playlists (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        cover_url TEXT,
        user_id INTEGER,
        is_public INTEGER DEFAULT 1,
        play_count INTEGER DEFAULT 0,
        like_count INTEGER DEFAULT 0,
        song_count INTEGER DEFAULT 0,
        tags TEXT,
        status INTEGER DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
      )
    `);
    console.log('✅ 歌单表创建成功');

    // 创建歌单歌曲关联表
    await query(`
      CREATE TABLE IF NOT EXISTS playlist_songs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        playlist_id INTEGER NOT NULL,
        song_id INTEGER NOT NULL,
        sort_order INTEGER DEFAULT 0,
        added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id),
        FOREIGN KEY (song_id) REFERENCES songs(id),
        UNIQUE(playlist_id, song_id)
      )
    `);
    console.log('✅ 歌单歌曲关联表创建成功');

    // 创建用户收藏表
    await query(`
      CREATE TABLE IF NOT EXISTS user_favorites (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        song_id INTEGER NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (song_id) REFERENCES songs(id),
        UNIQUE(user_id, song_id)
      )
    `);
    console.log('✅ 用户收藏表创建成功');

    // 创建播放历史表
    await query(`
      CREATE TABLE IF NOT EXISTS play_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        song_id INTEGER NOT NULL,
        play_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        play_duration INTEGER DEFAULT 0,
        device_type TEXT,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (song_id) REFERENCES songs(id)
      )
    `);
    console.log('✅ 播放历史表创建成功');

    // 创建用户歌单收藏表
    await query(`
      CREATE TABLE IF NOT EXISTS user_playlist_likes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        playlist_id INTEGER NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (playlist_id) REFERENCES playlists(id),
        UNIQUE(user_id, playlist_id)
      )
    `);
    console.log('✅ 用户歌单收藏表创建成功');

    console.log('🎉 SQLite数据库初始化完成！');

  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
  }
};

// 运行初始化
if (require.main === module) {
  initDatabase();
}

module.exports = initDatabase;